package com.doeis.core.db;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.log4j.Logger;

public class DBHelper {
	static final QueryRunner queryRunner = new QueryRunner();
	private static final Logger log = Logger.getLogger(DBHelper.class);
	private static final ColumnListHandler<Object> columnListHandler = new ColumnListHandler<Object>() {
		protected Object handleRow(ResultSet rs) throws SQLException {
			Object obj = super.handleRow(rs);
			if ((obj instanceof BigInteger))
				return Long.valueOf(((BigInteger) obj).longValue());
			return obj;
		}
	};

	private static final ScalarHandler<Object> scaleHandler = new ScalarHandler<Object>() {
		public Object handle(ResultSet rs) throws SQLException {
			Object obj = super.handle(rs);
			if ((obj instanceof BigInteger))
				return Long.valueOf(((BigInteger) obj).longValue());
			return obj;
		}
	};

	private static final List<Class<?>> PrimitiveClasses = new ArrayList<Class<?>>();

	private static final boolean isPrimitive(Class<?> cls) {
		return (cls.isPrimitive()) || (PrimitiveClasses.contains(cls));
	}

	public static Connection getConnection(String dataSourceName) throws DBException {
		try {
			return DBManager.getConnection(dataSourceName);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	public static void startTransaction(String dataSourceName) throws DBException {
		try {
			getConnection(dataSourceName).setAutoCommit(false);
			log.debug("Transaction is start.");
		} catch (SQLException e) {
			throw new DBException("BeginTransaction Error", e);
		}
	}

	public static void endTransaction(String dataSourceName) throws DBException {
		try {
			Connection connection = getConnection(dataSourceName);
			connection.commit();
			connection.setAutoCommit(true);
			log.debug("Transaction is commited.");
		} catch (SQLException e) {
			throw new DBException("Commit Transaction Error", e);
		}
	}

	public static void rollBackTransaction(String dataSourceName) {
		try {
			getConnection(dataSourceName).rollback();
			log.debug("Commit is rollbacked.");
		} catch (Exception e) {
			log.error("Rollback Commit Error", e);
		}
	}

	@SuppressWarnings("unchecked")
	public static <T> T read(String dataSourceName, Class<?> beanClass, String sql, Object[] params) throws DBException {
		try {
			log.debug("query params:" + joinArray(params));
			return (T) queryRunner.query(getConnection(dataSourceName), sql, isPrimitive(beanClass) ? scaleHandler : new BeanHandler(beanClass),
					params);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	public static <T> T read(Class<?> beanClass, String sql, Object[] params) throws DBException {
		return read(DBManager.getDefaultDataSourceName(Boolean.TRUE.booleanValue()), beanClass, sql, params);
	}

	public static <T> List<T> query(String dataSourceName, Class<T> beanClass, String sql, Object[] params) throws DBException {
		try {
			log.debug("query params:" + joinArray(params));
			return (List) queryRunner.query(getConnection(dataSourceName), sql, isPrimitive(beanClass) ? columnListHandler : new BeanListHandler(
					beanClass), params);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	public static <T> List<T> query(Class<T> beanClass, String sql, Object[] params) throws DBException {
		return query(DBManager.getDefaultDataSourceName(Boolean.TRUE.booleanValue()), beanClass, sql, params);
	}

	public static <T> List<T> querySlice(String dataSourceName, Class<T> beanClass, String sql, int page, int num, Object[] params)
			throws DBException {
		if ((page < 0) || (num < 0))
			throw new IllegalArgumentException("Illegal parameter of 'page' or 'count', Must be positive.");
		int from = (page - 1) * num;
		num = num > 0 ? num : 2147483647;
		return query(dataSourceName, beanClass, sql + " LIMIT ?,?",
				ArrayUtils.addAll(params, new Object[] { Integer.valueOf(from), Integer.valueOf(num) }));
	}

	public static <T> List<T> querySlice(Class<T> beanClass, String sql, int page, int num, Object[] params) throws DBException {
		return querySlice(DBManager.getDefaultDataSourceName(Boolean.TRUE.booleanValue()), beanClass, sql, page, num, params);
	}

	public static Integer count(String dataSourceName, String sql, Object[] params) throws DBException {
		try {
			log.debug("count params:" + joinArray(params));
			Number num = (Number) queryRunner.query(getConnection(dataSourceName), sql, scaleHandler, params);
			return Integer.valueOf(num != null ? num.intValue() : -1);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	public static Integer count(String sql, Object[] params) throws DBException {
		return count(DBManager.getDefaultDataSourceName(Boolean.TRUE.booleanValue()), sql, params);
	}

	public static int update(String dataSourceName, String sql, Object[] params) throws DBException {
		try {
			log.debug("update params:" + joinArray(params));
			return queryRunner.update(getConnection(dataSourceName), sql, params);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	public static int update(String sql, Object[] params) throws DBException {
		return update(DBManager.getDefaultDataSourceName(Boolean.FALSE.booleanValue()), sql, params);
	}

	public static int[] batch(String dataSourceName, String sql, Object[][] params) throws DBException {
		try {
			return queryRunner.batch(getConnection(dataSourceName), sql, params);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	public static int[] batch(String sql, Object[][] params) throws DBException {
		return batch(DBManager.getDefaultDataSourceName(Boolean.FALSE.booleanValue()), sql, params);
	}

	public static int[] batch(String dataSourceName, String sql, List<Object> beans, String[] propertyNames) throws DBException {
		PreparedStatement pstmt = null;
		try {
			Connection conn = getConnection(dataSourceName);
			pstmt = conn.prepareStatement(sql);
			for (Iterator<Object> localIterator = beans.iterator(); localIterator.hasNext();) {
				Object bean = localIterator.next();
				queryRunner.fillStatementWithBean(pstmt, bean, propertyNames);
				pstmt.addBatch();
			}
			int[] arrayOfInt = pstmt.executeBatch();
			return arrayOfInt;
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				throw new DBException(e);
			}
		}
	}

	public static int[] batch(String sql, List<Object> beans, String[] propertyNames) throws DBException {
		return batch(DBManager.getDefaultDataSourceName(Boolean.FALSE.booleanValue()), sql, beans, propertyNames);
	}

	public static QueryRunner getQueryrunner() {
		return queryRunner;
	}

	private static String joinArray(Object[] params) {
		StringBuffer sb = new StringBuffer();
		if (params == null) {
			return "";
		}
		Object[] arrayOfObject = params;
		int j = params.length;
		for (int i = 0; i < j; i++) {
			Object o = arrayOfObject[i];
			sb.append(o + ",");
		}
		return sb.toString();
	}
}